package com.zk.h2demo.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

@RestController
@RequestMapping("execute")
@PropertySource("classpath:application.properties")
public class CommonController {

	@Value("${spring.datasource.driver-class-name}")
	public String driverName;
	@Value("${spring.datasource.url}")
	public String url;
	@Value("${spring.datasource.username}")
	public String name;
	@Value("${spring.datasource.password}")
	public String password;

	@Autowired
	private static Connection conn;

	@GetMapping("sql")
	public Object executeSql(String sql) {
		JSONArray array = new JSONArray();
		try {
			if (conn == null || conn.isClosed())
				conn = DriverManager.getConnection(url, name, password);
			if (sql.startsWith("select")) {
				ResultSet resultSet = conn.createStatement().executeQuery(sql);
				ResultSetMetaData metaData = resultSet.getMetaData();
				int columnCount = metaData.getColumnCount();
				while (resultSet.next()) {
					JSONObject obj = new JSONObject();
					for (int i = 1; i < columnCount; i++) {
						String name = metaData.getColumnName(i);
						String value = resultSet.getString(name);
						obj.put(name, value);
					}
					array.add(obj);
				}
				return array;
			} else {
				return conn.createStatement().executeUpdate(sql)>0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return "失败：" + e.toString();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
